﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;

namespace GgcmsTBK
{
    public class DataHelper
    {
        private SqlConnection conn;
        private int TimeOut = 30;
        public static string DecodeOutputString(string outputstring)
        {
            //要替换的敏感字
            string str_Regex = @"(/sand/s)|(/sand/s)|(/slike/s)|(select/s)|(insert/s)|(delete/s)|(update/s[/s/S].*/sset)|(create/s)|(/stable)|(<[iframe|/iframe|script|/script])|(')|(/sexec)|(/sdeclare)|(/struncate)|(/smaster)|(/sbackup)|(/smid)|(/scount)";
            try
            {
                if ((outputstring != null) && (outputstring != String.Empty))
                {
                    Regex Regex = new Regex(str_Regex, RegexOptions.IgnoreCase);
                    MatchCollection matches = Regex.Matches(outputstring);
                    for (int i = 0; i < matches.Count; i++)
                        outputstring = outputstring.Replace(matches[i].Value, matches[i].Value.Substring(1, matches[i].Value.Length - 2));
                }
            }
            catch
            {
                return "";
            }
            return outputstring;
        }
        public DataHelper(string connstr)
        {
            conn = new SqlConnection(connstr);
        }
        public object ExecuteScalar(string cmdText, CommandType cmdType = CommandType.Text, params SqlParameter[] cmdParms)
        {
            
            SqlCommand cmd = new SqlCommand(cmdText, conn);
            if (cmdParms != null)
            {
                cmd.Parameters.AddRange(cmdParms);
            }
            cmd.CommandType = cmdType;
            cmd.CommandTimeout = TimeOut;
            OpenConnection();
            object obj = cmd.ExecuteScalar();
            CloseConnection();
            return obj;
        }
        public int ExecuteNonQuery(string cmdText, CommandType cmdType = CommandType.Text, List<SqlParameter> cmdParms=null)
        {
            SqlCommand cmd = new SqlCommand(cmdText, conn);
            if (cmdParms != null)
            {
                cmd.Parameters.AddRange(cmdParms.ToArray());
            }
            cmd.CommandType = cmdType;
            cmd.CommandTimeout = TimeOut;
            OpenConnection();
            int  obj = cmd.ExecuteNonQuery();
            CloseConnection();
            return obj;
        }
        public DataTable GetPageTable(string tabName, string cols, string query, string order, string sortby, int limit = 10, int PageNum=1)
        {
            StringBuilder sql = new StringBuilder();
            if (PageNum == 1)
            {
                sql.Append("select ");
                if (limit > 0)
                {
                    sql.Append("TOP " + limit.ToString() + " ");
                }
                sql.Append(string.IsNullOrWhiteSpace(cols) ? "* " : cols);
                sql.Append(" From " + tabName);
                if (!string.IsNullOrWhiteSpace(query))
                {
                    sql.Append(" WHERE " + query);
                }
                if (!string.IsNullOrWhiteSpace(order))
                {
                    string orderby = getOrderBy(order, sortby);

                    if (!string.IsNullOrWhiteSpace(order))
                    {
                        sql.Append(" ORDER BY " + orderby);
                    }
                }
            }
            else
            {
                string orderby = getOrderBy(order, sortby);
                orderby = string.IsNullOrWhiteSpace(orderby) ? "id DESC " : orderby;
                query = string.IsNullOrWhiteSpace(query) ? "" : " WHERE " + query;
                cols = string.IsNullOrWhiteSpace(cols) ? "*" : cols;
                int offset = (PageNum - 1) * limit;
                limit = offset + limit-1;
                //select t1.id,Content,Title FROM GgcmsArticles t1 INNER JOIN (select t2.Id  from(select row_number()  over (order by id DESC)r_num,Id FROM GgcmsArticles WHERE Id>23) t2 WHERE t2.r_num BETWEEN 2 and 4)t3 on t1.id=t3.id
                sql.Append("select t1.Id, " + cols + " FROM " + tabName + " t1 INNER JOIN ");
                sql.Append("(select t2.Id  from(select row_number()  over (order by " + orderby + ")r_num,Id FROM " + tabName + " " + query + ") t2 WHERE t2.r_num BETWEEN " + offset + " and " + limit + ") t3 on t1.Id=t3.Id");
            }
            DataTable dt = new DataTable(tabName);
            SqlDataAdapter da = new SqlDataAdapter(sql.ToString(), conn);
            da.Fill(dt);
            return dt;
        }
        private string getOrderBy(string order,string sortby)
        {
            string orderby = "";
            string[] o = order.Split(",".ToCharArray());
            string[] s = sortby.Split(",".ToCharArray());
            if (o.Length == s.Length)
            {
                List<string> ss = new List<string>();
                for (int i = 0; i < o.Length; i++)
                {
                    ss.Add(s[i] + " " + o[i]);
                }
                orderby = string.Join(",", ss);
            }
            return orderby;
        }
        public void OpenConnection()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            else
            {
                if (conn.State == ConnectionState.Broken)
                {
                    conn.Close();
                    conn.Open();
                }
            }
        }
        public void CloseConnection()
        {
            if (conn.State != ConnectionState.Closed)
            {
                conn.Close();
            }
        }
    }
    
}